package com.example.express.dao.impl;

import com.example.express.bean.Courier;
import com.example.express.dao.BaseCourierDao;
import com.example.express.util.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoImpl implements BaseCourierDao {
    private static final String SQL_CONSOLE_COURIER="SELECT " +
            "COUNT(id) data3_size," +
            "COUNT(TO_DAYS(registTime)=TO_DAYS(NOW()) OR NULL) data3_day " +
            "FROM t_courier";

    private static final String SQL_FIND_ALL = "SELECT * FROM t_courier";

    private static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM t_courier LIMIT ?,?";

    private static final String SQL_FIND_BY_ID = "SELECT * FROM t_courier WHERE id = ?";

    private static final String SQL_FIND_BY_PHONENUMBER = "SELECT * FROM t_courier WHERE phoneNumber = ?";

    private static final String SQL_INSERT = "INSERT INTO t_courier(name,phoneNumber,idNumber,password,registTime) VALUES (?,?,?,?,NOW())";

    private static final String SQL_UPDATE = "UPDATE t_courier SET name=?,phoneNumber=?,idNumber=?,password=? WHERE id=?";

    private static final String SQL_DELETE = "DELETE FROM t_courier WHERE id=?";

    private static final String SQL_UPDATE_LOGINTIME = "UPDATE t_courier SET loginTime=NOW() WHERE id = ?";

    private static final String SQL_UPDATE_SENDNUMBER = "UPDATE t_courier SET sendNumber=? WHERE id = ?";


    /**
     * 用于查询快递员总数和新增
     *
     * @return
     */
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE_COURIER);
            rs = state.executeQuery();
            while (rs.next()){
                data.put("data3_size",rs.getInt("data3_size"));
                data.put("data3_day",rs.getInt("data3_day"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    /**
     * 用于分页查询全部快递员信息
     *
     * @param limit
     * @param offset
     * @param pageNum
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNum) {
        List<Courier> data = new ArrayList<>();
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            if (limit){
                state = conn.prepareStatement(SQL_FIND_ALL_LIMIT);
                state.setInt(1,offset);
                state.setInt(2,pageNum);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String phoneNumber = rs.getString("phoneNumber");
                String idNumber = rs.getString("idNumber");
                String password = rs.getString("password");
                int sendNumber = rs.getInt("sendNumber");
                Timestamp registTime = rs.getTimestamp("registTime");
                Timestamp loginTime = rs.getTimestamp("loginTime");
                Courier courier = new Courier(id,name,phoneNumber,idNumber,password,sendNumber,registTime,loginTime);
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return data;
    }

    /**
     * 通过快递员编号查询快递信息
     *
     * @param id
     * @return
     */
    @Override
    public Courier findById(int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        Courier courier = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID);
            state.setInt(1,id);
            rs = state.executeQuery();
            while (rs.next()){
                String name = rs.getString("name");
                String phoneNumber = rs.getString("phoneNumber");
                String idNumber = rs.getString("idNumber");
                String password = rs.getString("password");
                int sendNumber = rs.getInt("sendNumber");
                Timestamp registTime = rs.getTimestamp("resgistTime");
                Timestamp loginTime = rs.getTimestamp("loginTime");
                courier = new Courier(id,name,phoneNumber,idNumber,password,sendNumber,registTime,loginTime);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return courier;
    }

    /**
     * 通过快递员手机号查询快递信息
     *
     * @param phoneNumber
     * @return
     */
    @Override
    public Courier findByPhoneNumber(String phoneNumber) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        Courier courier = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONENUMBER);
            state.setString(1,phoneNumber);
            rs = state.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String idNumber = rs.getString("idNumber");
                String password = rs.getString("password");
                int sendNumber = rs.getInt("sendNumber");
                Timestamp registTime = rs.getTimestamp("registTime");
                Timestamp loginTime = rs.getTimestamp("loginTime");
                courier = new Courier(id,name,phoneNumber,idNumber,password,sendNumber,registTime,loginTime);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,rs);
        }
        return courier;
    }

    /**
     * 快递员信息录入
     *
     * @param courier
     * @return
     */
    @Override
    public boolean insert(Courier courier) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1,courier.getName());
            state.setString(2,courier.getPhoneNumber());
            state.setString(3,courier.getIdNumber());
            state.setString(4,courier.getPassword());
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 快递员信息修改
     *
     * @param id
     * @param newCourier
     * @return
     */
    @Override
    public boolean update(int id, Courier newCourier) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newCourier.getName());
            state.setString(2,newCourier.getPhoneNumber());
            state.setString(3,newCourier.getIdNumber());
            state.setString(4,newCourier.getPassword());
            state.setInt(5,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 快递员信息删除
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更新登录时间
     *
     * @param id
     * @return
     */
    @Override
    public boolean updateLoginTime(int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_LOGINTIME);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更新派件数
     * @param sendNumber
     * @param id
     * @return
     */
    @Override
    public boolean updateSendNumber(String sendNumber, int id) {
        Connection conn = DruidUtils.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_SENDNUMBER);
            state.setString(1,sendNumber);
            state.setInt(2,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtils.close(conn,state,null);
        }
        return false;
    }
}
